# coding=utf-8

import codecs
import csv
import cx_Oracle
#import datetime
#import time

#D:\Users\chenchang313\Documents\工作\MySQL研究、引入工作\去IOE\PIF>sqlplus scott/tiger@10.50.120.60:11521/orcl
#
#SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 13 13:28:53 2014

USERNAME = 'scott'
PASSWORD = 'tiger'
DATABASE = '10.50.120.60:11521/orcl'


def default_error_handler(msg):
    return

def get_tab_data(conn, owner, table_name, csvfile, arraysize=100, error_handler = default_error_handler):

    cursor = conn.cursor()
    columns = cursor.execute("""select * from {OWNER}.{TABLE_NAME}""".format(OWNER=owner, TABLE_NAME=table_name))

    # 写入标题行
    csvfile.writerow([x[0] for x in cursor.description])
    coltypes = [x[1] for x in cursor.description]
    x = cursor.fetchmany(arraysize)
    while x:
        for row in x:
            cols = []
            for cpos, ctype in enumerate(coltypes):
                if row[cpos] is None:
                    cols.append("")
                elif ctype == cx_Oracle.BLOB:
                    cols.append("")
                elif ctype == cx_Oracle.CLOB:
                    cols.append("")
                elif ctype == cx_Oracle.DATETIME:
                    cols.append(str(row[cpos]))
                else:
                    cols.append(row[cpos])
#            print(len(cols), cols)
            csvfile.writerow(cols)
        x = cursor.fetchmany(arraysize)

    cursor.close()

def export_tab_data(conn, owner, table_name, filename, arraysize=100, error_handler = default_error_handler):
    with open(filename, 'w', encoding='utf-8', newline='') as f:
        csvfile =csv.writer(f, delimiter='\t', quotechar='"', quoting=csv.QUOTE_MINIMAL) 
        get_tab_data(conn, owner, table_name, csvfile, arraysize, error_handler)

IMP ="""load data local infile '{OWNER}.{TABLE_NAME}.txt'
into table {TABLE_NAME}
character set utf8
fields terminated by X'09'
optionally enclosed by '"'
lines terminated by '\\r\\n'
ignore 1 lines
(
    {COLS}
){PARAMS};
""" 

PARAM = "`{COL}`=case @{COL_PARAM} when \'\' then null else @{COL_PARAM} end"

def get_import_sql(conn, owner, table_name):
    cursor = conn.cursor()
    cursor.execute("""select * from {OWNER}.{TABLE_NAME} where rownum<=1""".format(OWNER=owner.lower(), TABLE_NAME=table_name.lower()))
    cols = []
    params = []
    for (col, nul) in [(x[0], x[6]) for x in cursor.description]:
#        print(col, nul)
        cols.append(("@%s" % col.replace('#','_').lower()) if nul else ("%s" % col.lower()))
        if nul:
            params.append((col.lower(), col.replace('#','_').lower()))

    cursor.close()
    #if params:
    ps = "\nset " + ",\n    ".join([PARAM.format(COL=x, COL_PARAM=y) for (x,y) in params]) if params else ""

    return IMP.format(OWNER=owner.lower(), TABLE_NAME=table_name.lower(), COLS=",\n    ".join(cols), PARAMS=ps)

def create_import_sql(conn, owner, table_name, filename, error_handler = default_error_handler):
    with open(filename, 'w', encoding='utf-8') as f:
        f.write(get_import_sql(conn, owner, table_name))
        f.write('commit;\n')
#        csvfile =csv.writer(f, delimiter='\t', quotechar='"', quoting=csv.QUOTE_MINIMAL) 
#        get_tab_data(conn, owner, table_name, csvfile, arraysize, error_handler)

def test_db():
    conn = cx_Oracle.connect(USERNAME, PASSWORD, DATABASE)

#    print(get_tab_columns(conn, "SCOTT", "TX"))
#    print(get_tab_primarykey(conn, "SCOTT", "TX"))
#    print(get_tab_indexes(conn, "SCOTT", "EMP"))
#    print(get_tab_definition(conn, "SCOTT", "TX"))
#    print(get_tab_modification(conn, "SCOTT", "TX"))
#    print(get_tab_definition(conn, "SCOTT", "EMP"))
#    export_tab_data(conn, "SCOTT", "TX", "scott.tx.txt")
    print(get_import_sql(conn, "SCOTT", "TX"))

    conn.close()

def test():
    print ('test')
    test_db()

if __name__ == "__main__":
    test()
